package com.zhdl.modules.common.production.repository;

import com.project.common.repository.BaseRepository;
import com.zhdl.modules.common.production.entity.ProductionRecord;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

/**
 *
 */



public interface ProductionRecordRepository extends BaseRepository<ProductionRecord, Integer> {

    List<ProductionRecord> findByFormulaId(Integer formulaId);

    ProductionRecord findByCreateDateLikeAndStatus(String createDate,Integer status);

    @Transactional
    @Modifying
    @Query("update ProductionRecord set status=0 where status=1")
    int updateStatusAll();

    @Query("select pd from ProductionRecord pd WHERE pd.createDate LIKE %:createDate%")
    List<ProductionRecord> findByCreateDateLike(String createDate);

    @Transactional
    @Modifying
    @Query("update ProductionRecord set productionOutput = :productionOutput where id = :Id")
    int updateProductionById(int Id,int productionOutput);

    @Query("select pd from ProductionRecord pd WHERE pd.status = :status AND pd.createDate LIKE %:createDate%")
    ProductionRecord findByCreateDateAndStatus(String createDate,Integer status);

    @Query(value = "SELECT SUBSTRING(create_date, 1, 10) AS createDate, SUM(productionOutput) AS productionOutput FROM common_records_production GROUP BY SUBSTRING(create_date, 1, 10) ORDER BY createDate DESC",nativeQuery = true)
    List<Map<String,String>> findByAll();

    @Query(value = "SELECT SUBSTRING(create_date, 1, 10) AS createDate, SUM(productionOutput) AS productionOutput FROM common_records_production GROUP BY SUBSTRING(create_date, 1, 10) ORDER BY createDate",nativeQuery = true)
    List<Map<String,String>> findBy();
}